package org.bugjail.dataaccess.jdbc.objects;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.bugjail.data.Person;
import org.bugjail.data.Suite;
import org.bugjail.data.TestCase;
import org.bugjail.data.TestCaseExecution;
import org.bugjail.data.TestDesign;
import org.bugjail.data.TestRound;
import org.bugjail.dataaccess.DAOFactory;
import org.bugjail.dataaccess.jdbc.connection.QueryException;
import org.bugjail.dataaccess.jdbc.connection.QueryManager;
import org.bugjail.dataaccess.objects.SuiteDAO;
import org.bugjail.dataaccess.objects.TestCaseDAO;
import org.bugjail.dataaccess.objects.TestCaseExecutionDAO;
import org.bugjail.dataaccess.objects.TestRoundDAO;

public class JDBCTestCaseExecutionDAO implements TestCaseExecutionDAO {

	private final String PROPERTIES_FILE = "/org/bugjail/dataaccess/jdbc/properties/testcaseexecutiondao.properties";

	private final String PROPERTY_RETRIEVE_BY_TESTROUND = "retrieve.bytestround";
	private final String PROPERTY_RETRIEVE_BY_TESTCASE = "retrieve.bytestcase";
	private final String PROPERTY_RETRIEVE_BY_TESTCASE_ON_TESTROUND = "retrieve.bytestcase.ontestround";
	private final String PROPERTY_RETRIEVE_BY_ID = "retrieve.byid";
	private final String PROPERTY_INSERT = "insert";
	private final String PROPERTY_EXECUTE = "execute";
	private final String PROPERTY_DELETE = "delete";
	private final String PROPERTY_ASSIGN = "assign";

	private TestCaseExecution extractTestCaseExecutionFromResultSet(
			ResultSet rs, QueryManager queryManager, TestRound testRound,
			TestCase testCase) throws SQLException {
		TestCaseExecution tce = new TestCaseExecution();
		int id = rs.getInt("id");
		tce.setId(id);

		if (testRound == null) {
			TestRoundDAO testRoundDao = DAOFactory.getDAOFactory()
					.getTestRoundDAO();
			tce.setTestRound(testRoundDao.retrieveById(rs
					.getInt("testround_id")));
		} else {
			tce.setTestRound(testRound);
		}

		if (testCase == null) {
			tce.setTestCase(DAOFactory.getDAOFactory().getTestCaseDAO()
					.retrieveByIdAndVersion(rs.getInt("testcase_id"),
							rs.getInt("testcase_version")));
		} else {
			if (testCase.getId() == rs.getInt("testcase_id")
					&& testCase.getVersion() == rs.getInt("testcase_version")) {
				tce.setTestCase(testCase);
			} else {
				tce.setTestCase(DAOFactory.getDAOFactory().getTestCaseDAO()
						.retrieveByIdAndVersion(rs.getInt("testcase_id"),
								rs.getInt("testcase_version")));
			}
		}

		tce.setResult(rs.getInt("result"));
		tce.setComments(rs.getString("comments"));
		try {
			tce.setExecutionDate(rs.getDate("executiondate"));
		} catch (Exception e) {
			tce.setExecutionDate(null);
		}

		if (rs.getInt("person_id") > 0) {
			tce.setAssignedUser(DAOFactory.getDAOFactory().getPersonDAO()
					.retrieveById(rs.getInt("person_id")));
		}

		tce.setCrsRelated(rs.getString("issuesrelated"));

		return tce;
	}

	@Override
	public void create(TestCaseExecution element) {
		// TODO Auto-generated method stub

	}

	@Override
	public void delete(TestCaseExecution element) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				deleteAction(queryManager, element);

				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	private void deleteAction(QueryManager queryManager, TestCaseExecution tce)
			throws SQLException {
		PreparedStatement statement = queryManager
				.getPreparedStatement(PROPERTY_DELETE);
		statement.setInt(1, tce.getId());
		statement.executeUpdate();
	}

	@Override
	public List<TestCaseExecution> retrieveAll() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public TestCaseExecution retrieveById(int id) {
		TestCaseExecution tce = null;
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_ID);
				statement.setInt(1, id);
				ResultSet rs = statement.executeQuery();
				if (rs.next()) {
					tce = extractTestCaseExecutionFromResultSet(rs,
							queryManager, null, null);
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return tce;
	}

	@Override
	public List<TestCaseExecution> retrieveByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void update(TestCaseExecution element) {
		// TODO Auto-generated method stub

	}

	@Override
	public void createBasedOnSuiteList(List<Suite> suites, TestRound testRound,
			Person assignedUser) {
		TestCaseDAO tcdao = DAOFactory.getDAOFactory().getTestCaseDAO();
		List<TestCase> tcList = new LinkedList<TestCase>();
		for (Suite suite : suites) {
			List<TestCase> tcs = tcdao.retrieveTestCasesOfSuite(suite);
			tcList.addAll(tcs);
		}
		createBasedOnTestCaseList(tcList, testRound, assignedUser);
	}

	@Override
	public void createBasedOnTestDesignList(List<TestDesign> testDesigns,
			TestRound testRound, Person assignedUser) {
		TestCaseDAO tcdao = DAOFactory.getDAOFactory().getTestCaseDAO();
		SuiteDAO suitedao = DAOFactory.getDAOFactory().getSuiteDAO();
		List<TestCase> tcList = new LinkedList<TestCase>();
		for (TestDesign testDesign : testDesigns) {
			List<Suite> suites = suitedao
					.retrieveSuitesOfTestDesign(testDesign);
			for (Suite suite : suites) {
				List<TestCase> tcs = tcdao.retrieveTestCasesOfSuite(suite);
				tcList.addAll(tcs);
			}
		}
		createBasedOnTestCaseList(tcList, testRound, assignedUser);
	}

	@Override
	public List<TestCaseExecution> retrieveByTestRound(TestRound testRound) {
		List<TestCaseExecution> tces = new LinkedList<TestCaseExecution>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_TESTROUND);
				statement.setInt(1, testRound.getId());
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					tces.add(extractTestCaseExecutionFromResultSet(rs,
							queryManager, testRound, null));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return tces;
	}

	@Override
	public void deleteBasedOnSuiteList(List<Suite> suites, TestRound testRound) {
		// TODO Auto-generated method stub

	}

	@Override
	public void deleteBasedOnTestDesignList(List<TestDesign> testDesigns,
			TestRound testRound) {
		// TODO Auto-generated method stub

	}

	@Override
	public List<TestCaseExecution> retrieveExecutionsOfTestCase(TestCase tc) {
		List<TestCaseExecution> tces = new LinkedList<TestCaseExecution>();
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_TESTCASE);
				statement.setInt(1, tc.getId());
				ResultSet rs = statement.executeQuery();
				while (rs.next()) {
					tces.add(extractTestCaseExecutionFromResultSet(rs,
							queryManager, null, tc));
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return tces;
	}

	@Override
	public void execute(TestCaseExecution tce) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_EXECUTE);
				statement.setInt(1, tce.getResult());
				statement.setString(2, tce.getComments());
				if (tce.getAssignedUser() != null) {
					statement.setInt(3, tce.getAssignedUser().getId());
				} else {
					statement.setInt(3, 0);
				}
				statement.setString(4, tce.getCrsRelated());
				statement.setInt(5, tce.getId());
				statement.executeUpdate();

				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public void createBasedOnTestCaseList(List<TestCase> tcList,
			TestRound testRound, Person assignedUser) {
		QueryManager queryManager = null;
		try {
			if (tcList.size() > 0) {
				try {
					queryManager = new QueryManager(PROPERTIES_FILE);
					for (TestCase tc : tcList) {
						TestCaseExecution tce = retrieveByTestCaseOnTestRound(
								tc, testRound);
						if (tce == null) {
							PreparedStatement statement = queryManager
									.getPreparedStatement(PROPERTY_INSERT);
							statement.setInt(1, testRound.getId());
							statement.setInt(2, tc.getId());
							statement.setInt(3, tc.getVersion());
							if (assignedUser != null) {
								statement.setInt(4, assignedUser.getId());
							} else {
								statement.setInt(4, 0);
							}
							statement.executeUpdate();
						}
					}
					QueryManager.commit(queryManager);
				} finally {
					QueryManager.close(queryManager);
				}
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public TestCaseExecution retrieveByTestCaseOnTestRound(TestCase tc,
			TestRound testRound) {
		TestCaseExecution tce = null;
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				PreparedStatement statement = queryManager
						.getPreparedStatement(PROPERTY_RETRIEVE_BY_TESTCASE_ON_TESTROUND);
				statement.setInt(1, tc.getId());
				statement.setInt(2, testRound.getId());
				ResultSet rs = statement.executeQuery();
				if (rs.next()) {
					tce = extractTestCaseExecutionFromResultSet(rs,
							queryManager, null, tc);
				}
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			throw new QueryException(e.getMessage());
		}
		return tce;
	}

	@Override
	public void assignTestCaseExecutionListToPerson(
			List<TestCaseExecution> tces, Person person) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				for (TestCaseExecution tce : tces) {
					PreparedStatement statement = queryManager
							.getPreparedStatement(PROPERTY_ASSIGN);
					if (person != null) {
						statement.setInt(1, person.getId());
					} else {
						statement.setInt(1, 0);
					}
					statement.setInt(2, tce.getId());
					statement.executeUpdate();
				}
				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

	@Override
	public void deleteBasedOnList(List<TestCaseExecution> tces) {
		QueryManager queryManager = null;
		try {
			try {
				queryManager = new QueryManager(PROPERTIES_FILE);
				
				for (TestCaseExecution testCaseExecution : tces) {
					deleteAction(queryManager, testCaseExecution);
				}

				QueryManager.commit(queryManager);
			} finally {
				QueryManager.close(queryManager);
			}
		} catch (Exception e) {
			QueryManager.rollback(queryManager);
			throw new QueryException(e.getMessage());
		}
	}

}
